package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import model.Dept;
import model.Duty;
import model.Emp;
import model.EmpTransfer;
import model.dept_duty_emp;
import util.DBUtil;
import util.Page;
import dao.dept_duty_empDAO;

public class dept_duty_empDAOImpl implements dept_duty_empDAO {

	private DBUtil dbu = new DBUtil();
	private Connection conn = null;
	private PreparedStatement pstat = null;
	private ResultSet rs = null;
	public Page<dept_duty_emp> getAll(int pageNo, int pageSize) {
		Page<dept_duty_emp> page = new Page<dept_duty_emp>(pageNo, pageSize);
		int totalCount = 0;
		List<dept_duty_emp> list = new ArrayList<dept_duty_emp>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from dept_duty_emp";
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select dde.id,dde.deptid,dde.dutyid,dde.empnum,dde.empnum-(select count(*)" 
+"from emp e where e.deptid=dde.deptid and e.dutiesid=dde.dutyid) as queyuan from dept_duty_emp dde) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setInt(2, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				dept_duty_emp dde=new dept_duty_emp();
				dde.setId(rs.getInt("id"));
				
				Dept dept=new DeptDAOImpl().getDeptById(rs.getInt("deptid"));
				dde.setDept(dept);
				
				Duty duty=new DutyDAOImpl().getDutyById(rs.getInt("dutyid"));
				dde.setDuty(duty);
				dde.setEmpnum(rs.getInt("empnum"));
				dde.setQuenum(rs.getInt(5));
				list.add(dde);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}
	public Page<dept_duty_emp> getByDept(int pageNo, int pageSize, Dept dept) {
		Page<dept_duty_emp> page = new Page<dept_duty_emp>(pageNo, pageSize);
		int totalCount = 0;
		List<dept_duty_emp> list = new ArrayList<dept_duty_emp>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from dept_duty_emp where deptid=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, dept.getId());
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select dde.id,dde.deptid,dde.dutyid,dde.empnum,dde.empnum-(select count(*)" 
+"from emp e where e.deptid=dde.deptid and e.dutiesid=dde.dutyid) as queyuan from dept_duty_emp dde) s where rownum <=? and deptid=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setInt(2, dept.getId());
			pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				dept_duty_emp dde=new dept_duty_emp();
				dde.setId(rs.getInt("id"));
				dde.setDept(dept);
				Duty duty=new DutyDAOImpl().getDutyById(rs.getInt("dutyid"));
				dde.setDuty(duty);
				dde.setEmpnum(rs.getInt("empnum"));
				dde.setQuenum(rs.getInt(5));
				list.add(dde);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}
	public Page<dept_duty_emp> getByDuty(int pageNo, int pageSize, Duty duty) {
		Page<dept_duty_emp> page = new Page<dept_duty_emp>(pageNo, pageSize);
		int totalCount = 0;
		List<dept_duty_emp> list = new ArrayList<dept_duty_emp>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from dept_duty_emp where dutyid=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, duty.getId());
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select dde.id,dde.deptid,dde.dutyid,dde.empnum,dde.empnum-(select count(*)" 
+"from emp e where e.deptid=dde.deptid and e.dutiesid=dde.dutyid) as queyuan from dept_duty_emp dde) s where rownum <=? and dutyid=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setInt(2, duty.getId());
			pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				dept_duty_emp dde=new dept_duty_emp();
				dde.setId(rs.getInt("id"));
				
				Dept dept=new DeptDAOImpl().getDeptById(rs.getInt("deptid"));
				dde.setDept(dept);
				dde.setDuty(duty);
				dde.setEmpnum(rs.getInt("empnum"));
				dde.setQuenum(rs.getInt(5));
				list.add(dde);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}
	public boolean addDeptDuty(dept_duty_emp deptduty) {
		boolean flag = false;
		try {
			conn = dbu.getConnection();
			String sql = "insert into dept_duty_emp(id,deptid,dutyid,empnum) values(seq_dde.nextval,?,?,?)";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, deptduty.getDept().getId());
			pstat.setInt(2, deptduty.getDuty().getId());
			pstat.setInt(3, deptduty.getEmpnum());
			pstat.executeUpdate();
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}
	public boolean deleteDeptDuty(dept_duty_emp deptduty) {
		boolean flag = false;
		try {
			conn = dbu.getConnection();
			String sql = "delete dept_duty_emp where deptid=? and dutyid=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, deptduty.getDept().getId());
			pstat.setInt(2, deptduty.getDuty().getId());
			pstat.executeUpdate();
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}
	public boolean updateDeptDuty(dept_duty_emp deptduty) {
		boolean flag = false;
		try {
			conn = dbu.getConnection();
			String sql = "update dept_duty_emp set empnum=? where deptid=? and dutyid=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(2, deptduty.getDept().getId());
			pstat.setInt(3, deptduty.getDuty().getId());
			pstat.setInt(1, deptduty.getEmpnum());
			pstat.executeUpdate();
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}
	public boolean updateByDept(int olddeptid, int newdeptid) {
		boolean flag = false;
		try {
			conn = dbu.getConnection();
			String sql = "update dept_duty_emp set deptid=? where deptid=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, newdeptid);
			pstat.setInt(2, olddeptid);
			pstat.executeUpdate();
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

}
